﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;

namespace STT.Layer.Connection
{
    public class Data
    {
        public Data()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        //public string str = "Data Source=EMSI;Initial Catalog=WebKhachSan;Integrated Security=True";
        public string msg;
        //public static SqlConnection con;

        public string str = ConfigurationManager.ConnectionStrings["DTNHConnectionString"].ConnectionString;
        public string module = null;
        public Control ct;

        //Mở kết nối
        public void OpenConnection(SqlConnection SqlCon)
        {
            try
            {
                //SqlCon = new SqlConnection(str);
                SqlCon.Open();
                msg = "Kết nối thành công";
            }
            catch (Exception)
            {
                msg = "Kết nối thất bại";
            }
            //MessageBox.Show(msg, "Thong bao", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }


        //Đóng kết nối
        public void CloseConnection(SqlConnection SqlCon)
        {
            SqlCon.Close();
            SqlCon.Dispose();
        }


        //Đổ dũ liệu lên SqlDataReder
        public SqlDataReader GetSqlDr(string sql)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    return dr;
                }
            }
        }

        ////Đổ dữ liệu lên Dataset
        public DataSet GetDataset(string sql)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                using (SqlDataAdapter da = new SqlDataAdapter(sql, cn))
                {
                    using (DataSet ds = new DataSet())
                    {
                        ds.Clear();
                        da.Fill(ds);
                        return ds;
                    }
                }
            }
        }

        ////Đổ dữ liệu lên Datatable
        public DataTable GetDataTable(string sql)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                using (SqlDataAdapter da = new SqlDataAdapter(sql, cn))
                {
                    using (DataTable dt = new DataTable())
                    {
                        dt.Clear();
                        da.Fill(dt);
                        return dt;
                    }
                }
            }
        }
        //Sử Dụng Procedure
        public DataTable GetDataTable2(string sql)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            dt.Clear();
                            da.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
        }
        //Lấy dữ liệu theo Id
        public DataTable GetDataTableById(string sql, string id)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = Convert.ToInt32(id);
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            dt.Clear();
                            da.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
        }
        //Lấy dữ liệu
        public DataTable GetDataTableById2(string sql, string id)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@id", SqlDbType.NVarChar).Value = id;
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            dt.Clear();
                            da.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
        }
        public DataTable GetDataTableById3(string sql, string id,string id2)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@id", SqlDbType.NVarChar).Value = id;
                    cmd.Parameters.Add("@Namhoc",SqlDbType.NVarChar).Value=id2;
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            dt.Clear();
                            da.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
        }
        //Kiểm tra dữ liệu có trung không
        public bool CheckIndentical(string sql, string input)
        {
            using (SqlConnection cn = new SqlConnection(str))
            {
                OpenConnection(cn);
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {

                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader dr = cmd.ExecuteReader();

                    bool kt = false;
                    while (dr.Read())
                    {
                        if (dr[0].ToString().Trim().ToLower() == input.ToString().Trim().ToLower()) kt = true;
                    }
                    return kt;
                }
            }
        }

        public bool CheckIndenticals(string sql, string DauVao1, string DauVao2)
        {
            using (SqlConnection cn = new SqlConnection(sql))
            {
                OpenConnection(cn);
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader dr = cmd.ExecuteReader();

                    bool kt = false;
                    while (dr.Read())
                    {
                        if ((dr[0].ToString().Trim().ToLower() == DauVao1.ToString().Trim().ToLower()) && (dr[1].ToString().Trim().ToLower() == DauVao2.ToString().Trim().ToLower())) kt = true;
                    }

                    return kt;
                }
            }

        }
        //public bool kt_key(string key, string query, string thamso)
        //{
        //    ketnoi.connect();
        //    SqlCommand cmd = new SqlCommand(query, ketnoi.con);
        //    cmd.CommandType = CommandType.StoredProcedure;
        //    cmd.Parameters.Add(thamso, SqlDbType.NChar).Value = key;
        //    SqlDataAdapter da = new SqlDataAdapter();
        //    da.SelectCommand = cmd;
        //    DataTable dt = new DataTable();
        //    da.Fill(dt);
        //    ketnoi.disconnect();
        //    if (dt.Rows.Count > 0)
        //        return false;
        //    else return true;
        //}
    }
}